﻿// =================================================================== 
// 项目说明
//====================================================================
// lsc @Copy Right 2019
// 文件： UserInfoRepository.cs
// 项目名称：斑马车务
// 创建时间：2019-07-31
// 负责人：lsc
// ===================================================================
using System;
using System.Text;
using System.Collections.Generic;
using YouWei.Infrastructure.Entity;
using Dapper.Extensions;
using YouWei.Infrastructure.Models;
using Dapper;
using YouWei.Utility;

namespace YouWei.Infrastructure.Repository
{
    /// <summary>
    ///用户表 仓储层
    /// </summary>	
    public class UserInfoRepository : BaseRepository<UserInfo>
    {

        #region 构造

        public UserInfoRepository() : base() { }

        public UserInfoRepository(string connectionString) : base(connectionString) { }

        public UserInfoRepository(DatabaseContext dbContext) : base(dbContext) { }

        #endregion

        /// <summary>
        /// 查询用户列表
        /// 此方法仅用于管理员
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public Page<UserInfoPageModel> Pages(int pageIndex, int pageSize, UserSearchModel model)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT T1.*,b.cName,b.pName,b.locationName,r.CardCode,r.PayState FROM [UserInfo] T1 WITH(NOLOCK)");
            //注：base_locationAll表里的locationId是主键，userinfo表里的location是地区，意义不一样。所以这里拿Hcity=locationid   liucx
            sb.Append(" LEFT JOIN BaseLocation_All b WITH(NOLOCK) ON T1.HCity= b.locationId");
            sb.Append(" LEFT JOIN UserCardRela r WITH(NOLOCK) ON T1.UserId=r.UserId");
            if (model.CarGroupId > 0)
            {              
                sb.Append(" left join wxgroupuser T2 with(nolock) on T2.userid = T1.UserID");
            }          

            sb.Append(" WHERE T1.IsActvie=1");
            var dparams = new DynamicParameters();
            if (model != null)
            {
                AddParams(model, sb, dparams);
            }

            sb.Append(" ORDER BY T1.UserID DESC");
            return dbContext.Connection.Page<UserInfoPageModel>(pageIndex, pageSize, sb.ToString(), dparams);
        }

        /// <summary>
        /// 查询用户列表
        /// 获取特定用户的子用户
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageSize"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public Page<UserInfo> Pages(int pageIndex, int pageSize, int userId, UserSearchModel model)
        {
            StringBuilder sb = new StringBuilder("SELECT T1.* FROM UserRelation T WITH(NOLOCK) ");
            sb.Append(" LEFT JOIN[UserInfo] T1 WITH(NOLOCK) ON T.ChildUserID = T.UserID");
            sb.Append(" WHERE T.UserID = @UserID");
            var dparams = new DynamicParameters();
            dparams.Add("UserID", userId, System.Data.DbType.Int32);
            if (model != null)
            {
                AddParams(model, sb, dparams);
            }
            sb.Append(" ORDER BY T1.UserID DESC");
            return Pages(pageIndex, pageSize, sb.ToString(), dparams);
        }

        /// <summary>
        /// 查询公司下的用户列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageSize"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public Page<UserInfo> PagesByCompanyID(int pageIndex, int pageSize, int companyId, UserSearchModel model)
        {
            StringBuilder sb = new StringBuilder("SELECT T1.* FROM UserCompanyRel T WITH(NOLOCK) ");
            sb.Append(" INNER JOIN[UserInfo] T1 WITH(NOLOCK) ON T.UserID = T1.UserID");
            sb.Append(" WHERE T.CompanyID = @CompanyID AND T1.IsActvie=1 AND T.IsActvie=1");
            var dparams = new DynamicParameters();
            dparams.Add("CompanyID", companyId, System.Data.DbType.Int32);
            if (model != null)
            {
                AddParams(model, sb, dparams);
            }
            sb.Append(" ORDER BY T1.UserID DESC");
            return Pages(pageIndex, pageSize, sb.ToString(), dparams);
        }

        /// <summary>
        /// 根据手机号获取用户信息
        /// </summary>
        /// <param name="mobile"></param>
        /// <returns></returns>
        public UserInfo GetUserInfoByMobile(string mobile)
        {
            StringBuilder sb = new StringBuilder("SELECT * FROM UserInfo T WITH(NOLOCK) ");
            sb.Append(" WHERE T.Mobile = @Mobile");
            return FirstOrDefault(sb.ToString(), new { Mobile = mobile });
        }

        /// <summary>
        /// 根据OpenID获取用户信息
        /// </summary>
        /// <param name="OpenID"></param>
        /// <returns></returns>
        public UserInfo GetUserInfoByOpenID(string OpenID)
        {
            StringBuilder sb = new StringBuilder("SELECT * FROM UserInfo T WITH(NOLOCK) ");
            sb.Append(" WHERE T.OpenID = @OpenID");
            return FirstOrDefault(sb.ToString(), new { OpenID = OpenID });
        }
        /// <summary>
        /// 获取一个随机用户信息
        /// </summary>
        /// <param name="OpenID"></param>
        /// <returns></returns>
        public UserInfo GetUserInfoRandom()
        {
            StringBuilder sb = new StringBuilder("select top 1 * from UserInfo where UserType=3 order by newid()  ");
            return FirstOrDefault(sb.ToString());
        }

        /// <summary>
        /// 根据openid获取用户信息
        /// </summary>
        /// <param name="openId"></param>
        /// <returns></returns>
        public WxUserDto GetUserByOpenID(string openId)
        {
            StringBuilder sb = new StringBuilder("SELECT T.WeChartUserID,T.OpenID,t.UnionID,T.NickName,T.HeadImgUrl,T1.UserHead,T1.UserID,T1.UserName,T1.Mobile AS UserMobile,T1.VehicleStatus,T.Sex FROM WeChartUser T WITH(NOLOCK) ");
            sb.Append(" LEFT JOIN UserInfo T1 WITH(NOLOCK) ON T.OpenID=T1.OpenID");
            sb.Append(" WHERE T.OpenID = @OpenID");
            return dbContext.Connection.QueryFirstOrDefault<WxUserDto>(sb.ToString(), new { OpenID = openId });
        }

        public void UpdateLevel(int level,int userId)
        {

        }

        /// <summary>
        /// 构建查询条件
        /// </summary>
        /// <param name="model"></param>
        /// <param name="sb"></param>
        /// <param name="dparams"></param>
        private void AddParams(UserSearchModel model, StringBuilder sb, DynamicParameters dparams)
        {
            if (!string.IsNullOrEmpty(model.Mobile))
            {
                sb.Append(" AND T1.Mobile=@Mobile");
                dparams.Add("Mobile", model.Mobile, System.Data.DbType.String);
            }

            if (model.HCity > 0)
            {
                sb.Append(" AND T1.HCity=@HCity");
                dparams.Add("HCity", model.HCity, System.Data.DbType.Int32);
            }
            else if (model.HProince > 0)
            {
                sb.Append(" AND T1.HProince=@HProince");
                dparams.Add("HProince", model.HProince, System.Data.DbType.Int32);
            }

            if (!string.IsNullOrEmpty(model.BCreateDate))
            {
                sb.Append(" AND T1.CreateDate>@BCreateDate");
                dparams.Add("BCreateDate", EConvert.ToDateTime(model.BCreateDate).Date, System.Data.DbType.DateTime);
            }

            if (!string.IsNullOrEmpty(model.ECreateDate))
            {
                sb.Append(" AND T1.CreateDate<@ECreateDate");
                dparams.Add("ECreateDate", EConvert.ToDateTime(model.ECreateDate).Date.AddDays(1), System.Data.DbType.DateTime);
            }

            if (model.CarGroupId > 0)
            {
                sb.Append(" AND T2.CarGroupId=@CarGroupId");
                dparams.Add("CarGroupId", model.CarGroupId, System.Data.DbType.Int32);
            }

            if (!string.IsNullOrWhiteSpace(model.UserName))
            {
                sb.Append(" AND charindex(@username,T1.username)>0");
                dparams.Add("username", model.UserName, System.Data.DbType.String);
            }

            if (model.CardLevel > 0)
            {
                sb.Append(" AND r.CardId=@CardId");
                dparams.Add("CardId", model.CardLevel, System.Data.DbType.Int32);
            }

        }

      /// <summary>
      /// 设置用户有效
      /// </summary>
      /// <param name="userId"></param>
        public void SetUserValid(int userId)
        {
            string sql = "UPDATE UserInfo SET VehicleStatus=2 WHERE UserID=@UserID ";
            Execute(sql, new { UserID = userId });
        }
    }
}